using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace MacViet.DataBaseAPI
{
    /// <summary>
    /// Description: Concrete DataProvider for table Appointments. Provides atomic API functions.
    /// Author:  phong.huynh
    /// Created: 8/13/2010 12:33:53 AM
    /// </summary>
    public class APPOINTMENTSDataAPI
    {
        private SQLHelper _dbTool = new SQLHelper();

        // ************************************
        // Implements function getByColumnName
        // ************************************

        public DataTable getByAPP_ID(System.Int32 APP_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID, APP_ID) };
            return _dbTool.executeReader("SP_SEL_APPOINTMENTS_getByAPP_ID", parm);
        }

        public DataTable getTimeByAPP_ID(System.Int32 APP_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._ACC_ID, ACC_ID),
                                      new SqlParameter(APPOINTMENTSDTO._APP_ID, APP_ID) };
            return _dbTool.executeReader("SP_GET_TIME_FROM_APP_ID", parm);
        }

        public DataTable getByEMP_ID(System.Int32 EMP_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._EMP_ID, EMP_ID) };
            return _dbTool.executeReader("SP_SEL_APPOINTMENTS_getByEMP_ID", parm);
        }
        public DataTable getByCLI_NAME()
        {

            return _dbTool.executeReader("SP_SEL_CLIENTS_getNAME");
        }


        public DataTable getByAPP_NAME_SPA_STATUS(System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("APP_GET_EMPLOYEE_SPA_STATUS",parm);
        }
        //----------------dang USE---------------
        public DataTable getBy_APP_ID(System.Int32 APP_ID,System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(ACCOUNTSDTO._ACC_ID,ACC_ID),
                                      new SqlParameter(APPOINTMENTSDTO._APP_ID, APP_ID) };
            return _dbTool.executeReader("SP_SEL_GetByAPP_ID", parm);
        }
        //----------------dang USE---------------
        public DataTable getByPAY_ID(System.Int32 PAY_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._PAY_ID, PAY_ID) };
            return _dbTool.executeReader("SP_SEL_APPOINTMENTS_getByPAY_ID", parm);
        }

        public DataTable getByCLI_ID(System.Int32 CLI_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._CLI_ID, CLI_ID) };
            return _dbTool.executeReader("SP_SEL_APPOINTMENTS_getByCLI_ID", parm);
        }

        public DataTable getBySPA_ID(System.Int32 SPA_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._SPA_ID, SPA_ID) };
            return _dbTool.executeReader("SP_SEL_APPOINTMENTS_getBySPA_ID", parm);
        }

        public DataTable getBySER_ID(System.Int32 SER_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._SER_ID, SER_ID) };
            return _dbTool.executeReader("SP_SEL_APPOINTMENTS_getBySER_ID", parm);
        }

        public DataTable getByAPP_DATE(System.DateTime APP_DATE)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_DATE, APP_DATE) };
            return _dbTool.executeReader("SP_SEL_APPOINTMENTS_getByAPP_DATE", parm);
        }

        public DataTable getByAPP_DATE_TIME(System.DateTime APP_DATE, System.DateTime APP_TIME,System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_DATE,APP_DATE),
                                  new SqlParameter(APPOINTMENTSDTO._APP_STARTTIME,APP_TIME),
                                 new SqlParameter(EMPLOYEESDTO._ACC_ID,ACC_ID)
                                  };
            return _dbTool.executeReader("SP_GET_APPOINTMENTS_EPLOYEE_BYDATE", parm);
        }


        public DataTable getByAPP_DATE_SPA(System.DateTime APP_DATE, System.DateTime APP_TIME, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_DATE,APP_DATE),
                                          new SqlParameter(APPOINTMENTSDTO._APP_STARTTIME,APP_TIME),
                                            new SqlParameter(EMPLOYEESDTO._ACC_ID,ACC_ID)
                                          };
            return _dbTool.executeReader("SP_GET_APPOINTMENTS_SPA_BYDATE", parm);
        }


        public DataTable getByAPP_STARTTIME(System.DateTime APP_STARTTIME)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_STARTTIME, APP_STARTTIME) };
            return _dbTool.executeReader("SP_SEL_APPOINTMENTS_getByAPP_STARTTIME", parm);
        }

        public DataTable getByAPP_STATUS(System.Int32 APP_STATUS)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_STATUS, APP_STATUS) };
            return _dbTool.executeReader("SP_SEL_APPOINTMENTS_getByAPP_STATUS", parm);
        }

        public DataTable getMore(System.Int32 ACC_ID, System.String APP_DATE)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._ACC_ID, ACC_ID),
                                   new SqlParameter(APPOINTMENTSDTO._APP_DATE,APP_DATE)};
            return _dbTool.executeReader("GET_INFO", parm);
        }

        public DataTable getOnlyIdByAppID(System.Int32 ACC_ID,System.Int32 APP_ID)
        {
            SqlParameter[] parm = {   new SqlParameter(APPOINTMENTSDTO._APP_ID, APP_ID), 
                                      new SqlParameter(CLIENTSDTO._ACC_ID, ACC_ID) };
            return _dbTool.executeReader("SP_SEL_ID_ByAPP_ID", parm);
        }

        public int CheckAppointmentByIdAndTime(System.Int32 EMP_ID, System.String APP_STARTTIME,System.DateTime APP_DATE ,System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {   new SqlParameter(APPOINTMENTSDTO._EMP_ID,EMP_ID),
                                      new SqlParameter(APPOINTMENTSDTO._APP_STARTTIME,APP_STARTTIME),
                                      new SqlParameter(APPOINTMENTSDTO._APP_DATE,APP_DATE),
                                      new SqlParameter(CLIENTSDTO._ACC_ID, ACC_ID) };
             int effectedRows = Convert.ToInt32(_dbTool.ExecuteScalar("CHECK_APPOINTMENT_BY_EMPID_AND_APPSTARTTIME", parm));
             return effectedRows;
        }
       


        // *************************************************
        // Implement function getAll/getByDynamicCondition
        // *************************************************

        public DataTable getAll()
        {
            return _dbTool.executeReader("SP_SEL_APPOINTMENTS_getAll");
        }


        // ********************************************************
        // Implement function getByPrimaryKeys / getByForeignKeys
        // In case table have composite primary/foreign keys
        // ********************************************************

        public DataTable getByPrimaryKeys(System.Int32 APP_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID, APP_ID) };
            return _dbTool.executeReader("SP_SEL_APPOINTMENTS_getByPrimaryKeys", parm);
        }

        public bool insertAPPOINTMENTS_ss(ref System.Int32 APP_ID, System.Int32 EMP_ID,
            System.Int32 PAY_ID, System.Int32 CLI_ID, System.Int32 SPA_ID, System.Int32 SER_ID,
            System.DateTime APP_DATE, System.DateTime APP_STARTTIME, System.String APP_STATUS)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID,1),
                                  new SqlParameter(APPOINTMENTSDTO._EMP_ID,EMP_ID),
                                  new SqlParameter(APPOINTMENTSDTO._PAY_ID,PAY_ID),
                                  new SqlParameter(APPOINTMENTSDTO._CLI_ID,CLI_ID),
                                  new SqlParameter(APPOINTMENTSDTO._SPA_ID,SPA_ID),
                                  new SqlParameter(APPOINTMENTSDTO._SER_ID,SER_ID),
                                  new SqlParameter(APPOINTMENTSDTO._APP_DATE,APP_DATE),
                                  new SqlParameter(APPOINTMENTSDTO._APP_STARTTIME,APP_STARTTIME),
                                  new SqlParameter(APPOINTMENTSDTO._APP_STATUS,APP_STATUS)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_APPOINTMENTS_values", parm);


            return (effectedRows == 1);
        }


        public bool insertAPPOINTMENTS(ref System.Int32 APP_ID, System.Int32 EMP_ID,
            System.Int32 CLI_ID, System.Int32 SPA_ID, System.Int32 SER_ID,
            System.DateTime APP_DATE, System.DateTime APP_STARTTIME, System.String APP_STATUS)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID,1),
                                          new SqlParameter(APPOINTMENTSDTO._EMP_ID,EMP_ID),
                                       
                                          new SqlParameter(APPOINTMENTSDTO._CLI_ID,CLI_ID),
                                          new SqlParameter(APPOINTMENTSDTO._SPA_ID,SPA_ID),
                                          new SqlParameter(APPOINTMENTSDTO._SER_ID,SER_ID),
                                          new SqlParameter(APPOINTMENTSDTO._APP_DATE,APP_DATE),
                                          new SqlParameter(APPOINTMENTSDTO._APP_STARTTIME,APP_STARTTIME),
                                          new SqlParameter(APPOINTMENTSDTO._APP_STATUS,APP_STATUS)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_APPOINTMENTS_values", parm);


            return (effectedRows == 1);
        }

        public int InsertAppointmentOutID(System.Int32 EMP_ID, System.Int32 CLI_ID, System.Int32 SPA_ID, System.DateTime APP_DATE, System.String APP_STARTTIME, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = {       new SqlParameter(APPOINTMENTSDTO._EMP_ID,EMP_ID),                                       
                                          new SqlParameter(APPOINTMENTSDTO._CLI_ID,CLI_ID),
                                          new SqlParameter(APPOINTMENTSDTO._SPA_ID,SPA_ID),                                          
                                          new SqlParameter(APPOINTMENTSDTO._APP_DATE,APP_DATE),
                                          new SqlParameter(APPOINTMENTSDTO._APP_STARTTIME,APP_STARTTIME),
                                          new SqlParameter(CLIENTSDTO._ACC_ID, ACC_ID)}; 
            int effectedRows = Convert.ToInt32(_dbTool.ExecuteScalar("SP_INS_APPOINTMENTS_OUT_ID", parm));
            return effectedRows;
        }  

       public bool insertAPPOINTMENTS_NOTIME(System.Int32 APP_ID, System.Int32 EMP_ID,System.Int32 ACC_ID,
            System.Int32 CLI_ID, System.Int32 SPA_ID,System.DateTime APP_DATE, System.String APP_STATUS)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID,APP_ID),
                                          new SqlParameter(APPOINTMENTSDTO._EMP_ID,EMP_ID),
                                          new SqlParameter(CLIENTSDTO._ACC_ID, ACC_ID),
                                          new SqlParameter(APPOINTMENTSDTO._CLI_ID,CLI_ID),
                                          new SqlParameter(APPOINTMENTSDTO._SPA_ID,SPA_ID),                                         
                                          new SqlParameter(APPOINTMENTSDTO._APP_DATE,APP_DATE),
                                          new SqlParameter(APPOINTMENTSDTO._APP_STATUS,APP_STATUS)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_APPOINTMENTS", parm);


            return (effectedRows == 1);
        }

       public bool insertAPPOINTMENT_SERVICES(System.Int32 APP_ID, System.Int32 SER_ID, System.String APP_STATUS)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID,APP_ID),
                                   new SqlParameter(APPOINTMENTSDTO._SER_ID,SER_ID),
                                  new SqlParameter(APPOINTMENTSDTO._APP_STATUS,APP_STATUS)};
            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_APPOINTMENT_SERVICES", parm);


            return (effectedRows == 1);
        }

        
         
        public bool insertAPPOINTMENTSAndClient(System.Int32 ACC_ID, System.String CLI_NAME, System.String CLI_ADDRESS, System.String CLI_EMAIL,
            System.String CLI_PHONE , System.Int32 EMP_ID,System.Int32 SPA_ID, System.Int32 SER_ID,
             System.DateTime APP_DATE, System.DateTime APP_STARTTIME, System.String APP_STATUS)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._CLI_ID,1),
                                      new SqlParameter(CLIENTSDTO._ACC_ID,ACC_ID),
                                    new SqlParameter(CLIENTSDTO._CLI_NAME,CLI_NAME),
                                    new SqlParameter(CLIENTSDTO._CLI_ADDRESS,CLI_ADDRESS),
                                    new SqlParameter(CLIENTSDTO._CLI_EMAIL,CLI_EMAIL),
                                    new SqlParameter(CLIENTSDTO._CLI_PHONE,CLI_PHONE),
                                    new SqlParameter(APPOINTMENTSDTO._APP_ID,1),
                                    new SqlParameter(APPOINTMENTSDTO._EMP_ID,EMP_ID),                                                                           
                                    new SqlParameter(APPOINTMENTSDTO._SPA_ID,SPA_ID),
                                    new SqlParameter(APPOINTMENTSDTO._SER_ID,SER_ID),
                                    new SqlParameter(APPOINTMENTSDTO._APP_DATE,APP_DATE),
                                    new SqlParameter(APPOINTMENTSDTO._APP_STARTTIME,APP_STARTTIME),
                                    new SqlParameter(APPOINTMENTSDTO._APP_STATUS,APP_STATUS)};

            int effectedRows = _dbTool.ExecuteNoneQuery("SP_INS_CLIENT_APPOINTMENT", parm);


            return (effectedRows == 1);
        }

        ///<summary>		
        /// Thu+.c hie^.n ca^u le^.nh update du+.a tre^n kho'a chi'nh
        ///</summary>

        public bool updateAPPOINTMENTS(System.Int32 APP_ID, System.Int32 EMP_ID, System.Int32 PAY_ID,
            System.Int32 CLI_ID, System.Int32 SPA_ID, System.Int32 SER_ID, System.DateTime APP_DATE,
            System.DateTime APP_STARTTIME, System.Int32 APP_STATUS)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID,APP_ID),
                                  new SqlParameter(APPOINTMENTSDTO._EMP_ID,EMP_ID),
                                  new SqlParameter(APPOINTMENTSDTO._PAY_ID,PAY_ID),
                                  new SqlParameter(APPOINTMENTSDTO._CLI_ID,CLI_ID),
                                  new SqlParameter(APPOINTMENTSDTO._SPA_ID,SPA_ID),
                                  new SqlParameter(APPOINTMENTSDTO._SER_ID,SER_ID),
                                  new SqlParameter(APPOINTMENTSDTO._APP_DATE,APP_DATE),
                                  new SqlParameter(APPOINTMENTSDTO._APP_STARTTIME,APP_STARTTIME),
                                  new SqlParameter(APPOINTMENTSDTO._APP_STATUS,APP_STATUS)};
            int affectRows = _dbTool.ExecuteNoneQuery("SP_UDP_APPOINTMENTS_update", parm);

            return affectRows == 1;
        }

        public bool UpdateStatus(System.Int32 APP_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID,APP_ID)};
            int affectRows = _dbTool.ExecuteNoneQuery("SP_INS_STATUSAPP_DOING_DONE", parm);

            return affectRows == 1;
        }

        public bool UpdateStatusWait_Doing(System.Int32 APP_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._ACC_ID,ACC_ID),
                                      new SqlParameter(APPOINTMENTSDTO._APP_ID,APP_ID)};
            int affectRows = _dbTool.ExecuteNoneQuery("UPDATE_STATUS_WAIT_DOING", parm);

            return affectRows == 1;
        }

                public bool UpdateStatusDone(System.Int32 APP_ID, System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._ACC_ID,ACC_ID),
                                      new SqlParameter(APPOINTMENTSDTO._APP_ID,APP_ID)};
            int affectRows = _dbTool.ExecuteNoneQuery("UPDATE_STATUS_DONE", parm);

            return affectRows == 1;
        }

        



        public bool UpdateStatusInApp_Ser(System.Int32 APP_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID, APP_ID) };
            int affectRows = _dbTool.ExecuteNoneQuery("SP_INS_APPSTATUS_DOING_DONE", parm);

            return affectRows == 1;
        }


        public bool UpdateTIPS(System.Int32 APP_ID,System.Int32 ACC_ID,System.Double TIPS)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID, APP_ID),
                                  new SqlParameter(CLIENTSDTO._ACC_ID,ACC_ID),
                                  new SqlParameter(APPOINTMENTSDTO._TIPS, TIPS)};
            int affectRows = _dbTool.ExecuteNoneQuery("sp_ud_Payment", parm);

            return affectRows == 1;
        }  
        // ********************************************************************************
        // Implementation of deleteAll/deteleByForeignKeys/deleteByPrimaryKeys function		
        // ********************************************************************************

        // return number of deleted rows		
        public int deleteAll()
        {
            return _dbTool.ExecuteNoneQuery("SP_DEL_APPOINTMENTS_deleteAll");
        }

        // return number of deleted rows
        public int deleteByPrimaryKeys(System.Int32 APP_ID)
        {
            SqlParameter[] parm = { new SqlParameter(APPOINTMENTSDTO._APP_ID, APP_ID) };
            return _dbTool.ExecuteNoneQuery("SP_DEL_APPOINTMENTS_deleteByPrimaryKeys", parm);
        }


        public int deleteByApp_Id(System.Int32 APP_ID,System.Int32 ACC_ID)
        {
            SqlParameter[] parm = { new SqlParameter(CLIENTSDTO._ACC_ID,ACC_ID),
                                      new SqlParameter(APPOINTMENTSDTO._APP_ID, APP_ID) };
            return _dbTool.ExecuteNoneQuery("DELETE_APP_ID", parm);
        }
    }
}